package com.xinzhitong.www.mapper;

import com.xinzhitong.www.enumeration.OrderType;
import com.xinzhitong.www.pojo.Consign;
import com.xinzhitong.www.pojo.ConsignRequest;
import com.xinzhitong.www.pojo.UserHasConsign;
import com.xinzhitong.www.provider.SelectSQLProvider;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
 * 委培表的相关操作
 *
 * @author 魏骆淳 徐鑫
 * @author 吴展鹏
 */
@Repository
public interface ConsignMapper {

    @Select("select consignName, trainOrganName from consign_apply_information_view where id = #{consignId}")
    Map<String, Object> selectConsignOutline(@Param("consignId") Integer consignId);


    @Select("select `MIN(picture)` from company_first_picture_view where companyId = #{companyId}")
    Map<String, Object> selectCompanyPicture(@Param("companyId") Integer companyId);

    @Select("select text, be_checked from consign_request where consign_id = #{consignId}")
    List<ConsignRequest> selectConsignRequest(@Param("consignId") Integer consignId);

    @Select("select username, consign_id " +
            "from user_has_consign " +
            "where username = #{username} and consign_id = #{consignId}")
    UserHasConsign selectConsignApplyInformation(
            @Param("username") String username,
            @Param("consignId") Integer consignId
    );

    @Insert("insert into user_has_consign(username, consign_id) " +
            "values (#{username}, #{consignId})")
    void insertConsignApplyInformation(
            @Param("username") String username,
            @Param("consignId") Integer consignId);

    @SelectProvider(type = SelectSQLProvider.class, method = "getSQLOfSelectByConditionsWithKeyWord")
    List<Map<String, Object>> selectConsignByConditions(
            @Param("table") String table,
            @Param("selectFields") String[] selectFields,
            @Param("keyWord") String keyWord,
            @Param("orderField") String orderField,
            @Param("orderType") OrderType orderType,
            @Param("conditions") Map<String, Object> conditions
    );

    /*
    *
    *
    *@author 吴展鹏
    * */

    @Select("select * from consign_outline_by_organid_view where state=#{state} and train_organ_id = #{organId}")
    List<Map<String,Object>> selectConsignByOrgan(
            @Param("organId")Integer organId,
            @Param("state")String state
    );

    @Select("select * from consign_outline_by_organid_view where train_organ_id = #{organId}")
    List<Map<String,Object>> selectAllStateConsignByOrgan(
            @Param("organId")Integer organId
    );

    @Update("Update post SET state = '已发布' where id =#{consignId}")
    Integer ConfirmStateOfConsign(Integer consignId);

    @Update("Update post SET publish_time = #{time} where id =#{consignId}")
    void UpdatePostPublishTime(String time,Integer consignId);

    @Update("Update consign SET train_organ_id = #{organId} where id =#{consignId}")
    void UpdateOrganIdOfConsign(Integer consignId,Integer organId);


    @Select("select id from train_organ where username = #{username}")
    Integer selectOrganIdByName(@Param("username") String username);

    @Select("select count from consign_count_view where id = #{consignId}")
    Map<String, Object> selectConsignCount(@Param("consignId") Integer consignId);

    @Select("select requirePopulation, count from consign_require_and_count_view where id = #{consignId}")
    Map<String, Object> selectConsignPopulation(@Param("consignId") Integer consignId);

    @Select("select startTime, endTime, consignLocation from consign_location_and_time_view where id = #{consignId}")
    Map<String, Object> selectConsignLocationAndTime(@Param("consignId") Integer consignId);

    @Select("select id from consign where id = #{consignId}")
    Consign selectConsignId(@Param("consignId") Integer consignId);

    @Select("select * from consign_information_view where consignId = #{consignId}")
    Map<String, Object> selectConsignInformation(@Param("consignId") Integer consignId);

    @Select("select dayDiff from user_consign_day_diff where username = #{username}")
    Map<String, Object> selectConsignDayDiff(@Param("username") String username);

    @Select("select consignDate " +
            "from user_has_consign_time " +
            "where username = #{username} and consignTime >= #{thisMonth} and consignTime < #{nextMonth}")
    List<Integer> selectConsignDate(
            @Param("username") String username,
            @Param("thisMonth") String thisMonth,
            @Param("nextMonth") String nextMonth
    );

    @Select("select datetime, consignName, consignId " +
            "from consign_system_message_view " +
            "where username = #{username} and consignId = #{consignId}")
    Map<String, Object> selectConsignSystemMessage(
            @Param("username") String username,
            @Param("consignId") Integer consignId
    );

    @Select("SELECT\n" +
            "count(0)\n" +
            "FROM\n" +
            "consign\n" +
            "INNER JOIN post ON consign.id=post.id\n" +
            "where company_id=#{companyId}")
    Integer countCondignOfTheCompany(@Param("companyId") Integer companyId);

    @Select("SELECT\n" +
            "Count(0)\n" +
            "FROM\n" +
            "company\n" +
            "INNER JOIN post ON post.company_id = company.id\n" +
            "INNER JOIN consign ON consign.id = post.id\n" +
            "INNER JOIN user_has_consign ON user_has_consign.consign_id = consign.id\n" +
            "WHERE\n" +
            "company.id=#{companyId}\n")
    Integer countHasUserOfConsignOfTheCompany(@Param("companyId") Integer companyId);

    @Delete("Delete from post  where id=#{id}")
    void deleteConsign(Integer id);

    @Select("SELECT username,datetime,realname,id,contact FROM user_enroll_consign_condition where " +
            "consignId=#{consignId}")
    List<Map<String, Object>> SelectConsignEnrollCondition(Integer consignId);

    @Select("SELECT last_login_time FROM user where username=#{username}")
    Map<String, Object> SelectLastTime(String username);

    @Select("SELECT * from consign_new_enroll where datetime<=#{time} and companyUsername=#{username}")
    List<Map<String, Object>> SelectNewConsign(Timestamp time, String username);

    @Update("Update post SET state='已下架' where id=#{id}")
    Integer updateStateOfConsign(Integer id);

    @Select("SELECT id,name,count,requirePopulation,publishTime,state" +
            " FROM consign_general where state LIKE #{state} and name LIKE #{name} and username=#{username}")
    List<Map<String, Object>> selectConsignGeneral(String name, String state, String username);

    @Select("select last_insert_id()")
    Integer selectLastInsertId();

    @Insert("insert into consign(id, start_time, end_time, text, deadline, other_notice) " +
            "values(#{id}, #{startTime}, #{endTime}, #{applyRequest}, #{deadline}, #{otherNotice})")
    void insertConsignInformation(
            Integer id,
            String startTime,
            String endTime,
            String deadline,
            String applyRequest,
            String otherNotice
    );

    @Select("select * from consign where id = #{consignId}")
    Consign selectConsign(Integer consignId);

    @Update("update consign set start_time = #{startTime}, end_time = #{endTime}, text = #{applyRequest}, " +
            "deadline = #{deadline}, other_notice = #{otherNotice} where id = #{consignId}")
    void updateConsign(
            Integer consignId,
            String startTime,
            String endTime,
            String deadline,
            String applyRequest,
            String otherNotice
    );

    @Select("select consignName, companyName, contact from consign_basic_information_view " +
            "where consignId = #{consignId}")
    Map<String, Object> selectConsignBasicInformation(@Param("consignId") Integer consignId);

    @Select("select enrollCount, clickCount, collectCount, shareCount from consign_index_view " +
            "where consignId = #{consignId}")
    Map<String, Object> selectConsignIndex(@Param("consignId") Integer consignId);

    @Select("select consignName, requirePopulation, enrollPopulation, release_time, state from recent_editor_consign " +
            "where username = #{username}")
    List<Map<String, Object>> selectRecentEditorConsign(String username);

}
